package CelestialDataAccess;

import com.sun.rowset.*;   //provides cached row set implementation
import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception
import java.text.SimpleDateFormat;
import java.util.Dictionary;
import java.util.List;

public class EmployeesDataAccess extends DataAccessBase{
    private final static SimpleDateFormat sdf =
            new SimpleDateFormat("mm-dd-yyyy");

    /*************************************************************************/
    /* Client methods */
    public int insertEmployee(Employee emp){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspInsertEmployee(?,?,?,?,?,?,?,?,?,?,?,?,?,)}");
            stm.setString("Username", emp.getUsername());
            stm.setBytes("Password", CryptService.encrypt(emp.getPassword()));
            stm.setInt("UserLevel", emp.getUserLevel());
            //Employee ID: IDENTITY
            stm.setDate("EmployedOn", java.sql.Date.valueOf(sdf.format(emp.getEmploymentDate())));

            stm.setString("FirstName", emp.getFirstName());
            stm.setString("LastName", emp.getLastName());
            stm.setString("Contact", emp.getContact());
            stm.setString("Email", emp.getEmail());
            stm.setDate("DOB", java.sql.Date.valueOf(sdf.format(emp.getDOB())));

            stm.setString("AddressLine1", emp.getAddressLine1());
            stm.setString("AddressLine2", emp.getAddressLine1());
            stm.setString("PostalCode", emp.getPostalCode());

            stm.setBoolean("IsActive", emp.isActive());

            stm.execute();
        }catch(Exception ex){

            return 0;
        }

        return 1;
    }
    /*----------------------------------------------------------------------*/
    public int updateEmployee(Employee emp){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspUpdateEmployee(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            stm.setBytes("Password", CryptService.encrypt(emp.getPassword()));
            stm.setInt("UserLevel", emp.getUserLevel());
            stm.setInt("EmployeeID", emp.getEmployeeID());
            stm.setDate("EmployedOn", java.sql.Date.valueOf(sdf.format(emp.getEmploymentDate())));

            stm.setString("FirstName", emp.getFirstName());
            stm.setString("LastName", emp.getLastName());
            stm.setString("Contact", emp.getContact());
            stm.setString("Email", emp.getEmail());
            stm.setDate("DOB", java.sql.Date.valueOf(sdf.format(emp.getDOB())));

            stm.setString("AddressLine1", emp.getAddressLine1());
            stm.setString("AddressLine2", emp.getAddressLine1());
            stm.setString("PostalCode", emp.getPostalCode());

            stm.setBoolean("IsActive", emp.isActive());

            stm.execute();
            stm.execute();
        }catch(Exception ex){
            return 0;
        }
         return 1;
    }
    /*----------------------------------------------------------------------*/
    public int deleteEmployee(Employee emp){

        CallableStatement stm;

        try{
            stm = cnn.prepareCall("{CALL uspDeletetEmployee(?)}");
            stm.setInt("EmployeeID", emp.getEmployeeID());

            stm.execute();
        }catch(SQLException ex){

            return 0;
        }
        return 1;
    }
    /*----------------------------------------------------------------------*/
    public Employee getEmployee(int emp_id) throws DataAccessException{

        CallableStatement stm;
        ResultSet rs;
        CachedRowSet crs;

        try{
            crs = new CachedRowSetImpl();
            stm = cnn.prepareCall("{Call uspGetEmployee(?)}");
            stm.setInt("EmployeeID", emp_id);

            rs = stm.executeQuery();
            crs.populate(rs);
            
            crs.last();
            if(crs.getRow()==0) return null;

        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }

        /* try create new User object */
        try{
            return Employee.createEmployee(crs);
        }catch (Exception ex){
            return null;
        }
    }
    /*----------------------------------------------------------------------*/
    public static int getNextEmployeeID(){

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        try{
            DataAccessBase.open();

            stm = cnn.prepareCall("{CALL uspGetNextEmployeeID}");
            rs = stm.executeQuery();

            crs = new CachedRowSetImpl();
            crs.populate(rs);

            DataAccessBase.close();
            
            assert crs.last()==true;
            crs.last();
            return crs.getInt(1);
            
        }catch(SQLException ex){
            return -1;
        }
    }
    /*----------------------------------------------------------------------*/
    public Employee validateLogin(String username, String password) throws DataAccessException{

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        /* get Employee records from database */
        try{
            crs = new CachedRowSetImpl();
            stm = cnn.prepareCall("{Call uspValidateEmployeeLogin(?,?)}");
            stm.setString("Username", username);
            stm.setBytes("Password", CryptService.encrypt(password));

            rs = stm.executeQuery();
            crs.populate(rs);

        }catch(Exception ex){
            throw new DataAccessException(ex.getMessage());
        }

        /* try create new User object */
        try{
            return Employee.createEmployee(crs);
        }catch (Exception ex){
            return null;
        }
    }
    /*----------------------------------------------------------------------*/
    public void fill(Dictionary<Integer, Employee> dic) throws DataAccessException{

       try{
            CachedRowSet crs = getEmployees();
            while(crs.next()){
                dic.put(crs.getInt("EmployeeID"), Employee.createEmployee(crs));
            }
       }catch (Exception ex){
           throw new DataAccessException(ex.getMessage());
       }
    }
    /*----------------------------------------------------------------------*/
    public void fill(List<Employee> lst) throws DataAccessException{

         lst.clear();
         try{
            CachedRowSet crs = getEmployees();
            while(crs.next()){
                lst.add(Employee.createEmployee(crs));
            }
       }catch (Exception ex){
           throw new DataAccessException(ex.getMessage());
       }
    }
    /************************************************************************/
    /* Helpers */
    private CachedRowSet getEmployees() throws SQLException{

        CachedRowSet crs;
        ResultSet rs;
        CallableStatement stm;

        /* retrieve Employee data from database */
        stm = cnn.prepareCall("{Call uspGetEmployees}");
        rs = stm.executeQuery();

        crs = new CachedRowSetImpl();
        crs.populate(rs);

        return crs;
    }
    /*************************************************************************/
    /* Test */
    public static void main(String[] args){

        Employee emp1, emp2;
        EmployeesDataAccess daEmployees;

        try{
           DataAccessBase.open();
           daEmployees = new EmployeesDataAccess();

           emp1 = daEmployees.validateLogin("chseng", "azsxdcfv");
           emp2 = daEmployees.getEmployee(1003);
           DataAccessBase.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            System.out.println(ex.toString());
            return;
        }

        if(emp1==null) return;
        
        System.out.println(emp1.getUsername() + "\t"
                           + emp1.getPassword() + "\t"
                           + emp1.getFirstName() + "\t"
                           + emp1.getUserLevel());
        System.out.println(emp2.getUsername());
        System.out.println(getNextEmployeeID());
    }
}
